﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HardwareAs
{
    public class SqlRequest
    {
        SqlConnection sb = new SqlConnection(ConfigurationManager.ConnectionStrings["HardwareConnectionString"].ConnectionString);
        SqlCommand MyCommand;
        SqlDataReader MyReader;
         String tag;
       
        /*
         * Add the manufacturers ,category and products to the Products page.
         * reade the details from the database
         *
         */
        public SqlDataReader AddFactory()
        {

            MyCommand = new SqlCommand("SelectAllManu", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            sb.Open();
            MyReader = MyCommand.ExecuteReader();
            return MyReader;
        }

        public SqlDataReader AddCategory(String s)
        {
            MyCommand = new SqlCommand("SelectCategoryWithparam", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@mID", SqlDbType.Int);
            MyCommand.Parameters["@mID"].Value = s;
            sb.Open();
            MyReader = MyCommand.ExecuteReader();
            return MyReader;
        }
        public SqlDataReader AddProducts(String s)
        {
            MyCommand = new SqlCommand("SelectProductWithparam", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@cID", SqlDbType.Int);
            MyCommand.Parameters["@cID"].Value = s;
            sb.Open();
            MyReader = MyCommand.ExecuteReader();
            return MyReader;
        }

        public String SearchInfo(String m, String c, String p)
        {
            MyCommand = new SqlCommand("SelectTagline", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@mID", SqlDbType.Int);
            MyCommand.Parameters["@mID"].Value = m;
            MyCommand.Parameters.Add("@cID", SqlDbType.Int);
            MyCommand.Parameters["@cID"].Value = c;
            MyCommand.Parameters.Add("@pID", SqlDbType.Int);
            MyCommand.Parameters["@pID"].Value = p;
            sb.Open();
            tag = MyCommand.ExecuteScalar().ToString();
            return tag;

        }

        //get the image path
        public String AddImage(String m, String c, String p)
        {
            MyCommand = new SqlCommand("GetImage", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@mID", SqlDbType.Int);
            MyCommand.Parameters["@mID"].Value = m;
            MyCommand.Parameters.Add("@cID", SqlDbType.Int);
            MyCommand.Parameters["@cID"].Value = c;
            MyCommand.Parameters.Add("@pID", SqlDbType.Int);
            MyCommand.Parameters["@pID"].Value = p;
            String url = MyCommand.ExecuteScalar().ToString();
            return url;
        }

        public String getDescription(String m, String c, String p)
        {
            MyCommand = new SqlCommand("GetDescription", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@mID", SqlDbType.Int);
            MyCommand.Parameters["@mID"].Value = m;
            MyCommand.Parameters.Add("@cID", SqlDbType.Int);
            MyCommand.Parameters["@cID"].Value = c;
            MyCommand.Parameters.Add("@pID", SqlDbType.Int);
            MyCommand.Parameters["@pID"].Value = p;
            sb.Open();
            String des = MyCommand.ExecuteScalar().ToString();
            return des;

        }


        //get data from FAQ table
        public  SqlDataReader myFAQ(String s)
        {
            String[] keywords =new String[]{"and","or"};
            String sqlcmd = "'%##%'";
            try
            {
                String[] tokens = s.Trim().Split(' ');
                foreach (String token in tokens)
                {
                    if (keywords.Contains(token.ToLower()))
                    {
                        sqlcmd += token +" QuestionLong like '%##%'";

                    }
                    else
                    {

                        sqlcmd = sqlcmd.Replace("##",token );
                    
                    }
           
                
                }


            }
            catch { }
            MyCommand = new SqlCommand("SearchFAQ", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.AddWithValue("@www", sqlcmd);
            MyCommand.Connection.Open();
            MyReader = MyCommand.ExecuteReader();
           return MyReader;
        
        
        }
        public SqlDataReader getAllFAQ()
        {
            MyCommand = new SqlCommand("SelectAllFAQ", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Connection.Open();
            MyReader = MyCommand.ExecuteReader();
            return MyReader;
        
        
        }
        //Check User Username and Password
        public bool CheckUser(String u, String p,out int isAdmin)
        {
            isAdmin = 0;
            MyCommand = new SqlCommand("AccountVerify",sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.AddWithValue("@user", u);
            MyCommand.Parameters.AddWithValue("@pass", Encrypt(p));
            MyCommand.Connection.Open();
            MyReader = MyCommand.ExecuteReader();
           
             if (MyReader.HasRows)
            {
                MyReader.Read();
                isAdmin = (int)MyReader["isAdmin"];
                
                return true;
            }
            return false;
            
        }
        public bool CheckUserExist(String email)
        {
            MyCommand = new SqlCommand("AccountExists", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.AddWithValue("@user", email);
            MyCommand.Connection.Open();
     

          if ( MyCommand.ExecuteReader().HasRows)
          {
              return false;
          }

          return true;
        
        
        }

        //add user to database
        public int AddUser(String FN, String LN, String em, String pass,String address,  int age,int isAdmin =0)
        {
           // String d =String.Format("INSERT INTO UserList(firstname,lastname,email,password,address,age) Values('{0}','{1}','{2}','{3}','{4}',{5})",FN,LN,em,Encrypt(pass),address,age);
            MyCommand = new SqlCommand("AddAccount", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.AddWithValue("@firstname", FN);
            MyCommand.Parameters.AddWithValue("@lastname", LN);
            MyCommand.Parameters.AddWithValue("@email", em);
            MyCommand.Parameters.AddWithValue("@password", Encrypt(pass));
            MyCommand.Parameters.AddWithValue("@address", address);
            MyCommand.Parameters.AddWithValue("@age", age);
            MyCommand.Parameters.AddWithValue("@isAdmin", isAdmin);
            MyCommand.Connection.Open();
            int i =MyCommand.ExecuteNonQuery();
            return i;
        
        
        }
        public String Encrypt(String de)
        {
        
        return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(de, "MD5").ToLower();
        
        }

        /*
         * get data from download table
         * and add data this table
         */ 
         
    
        public SqlDataReader AddDownload(String s)
        {
            MyCommand = new SqlCommand("SelectDownload", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@cId", SqlDbType.Int);
            MyCommand.Parameters["@cId"].Value = s;
            sb.Open();
            MyReader = MyCommand.ExecuteReader();
            return MyReader;
        }
        public String getDownloadDescription(String s)
        {
            MyCommand = new SqlCommand("SelectDownloadDescription", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@Id", SqlDbType.Int);
            MyCommand.Parameters["@Id"].Value = s;
            sb.Open();
            String des = MyCommand.ExecuteScalar().ToString();
            sb.Close();
            return des;
        }
        public String getDownloadUrl(String s)
        {
            MyCommand = new SqlCommand("SelectDownloadUrl", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@Id", SqlDbType.Int);
            MyCommand.Parameters["@Id"].Value = s;
            sb.Open();
            String des = MyCommand.ExecuteScalar().ToString();
            return des;
        }
        public void InsertDownload(String cid, String title, String des, String url, String uid)
        {
            MyCommand = new SqlCommand("InsertDownload", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            MyCommand.Parameters["@Title"].Value = title;
            MyCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            MyCommand.Parameters["@Description"].Value = des;
            MyCommand.Parameters.Add("@DownloadUrl", SqlDbType.NVarChar);
            MyCommand.Parameters["@DownloadUrl"].Value = url;
            MyCommand.Parameters.Add("@CategoryId", SqlDbType.Int);
            MyCommand.Parameters["@CategoryId"].Value = cid;
            MyCommand.Parameters.Add("@CustomerId", SqlDbType.NVarChar);
            MyCommand.Parameters["@CustomerId"].Value = uid;
            MyCommand.Parameters.Add("@Valid", SqlDbType.Bit);
            MyCommand.Parameters["@Valid"].Value = 0;
            MyCommand.Connection.Open();
            int i = MyCommand.ExecuteNonQuery();
        }
        public SqlDataReader AllCategory()
        {
            MyCommand = new SqlCommand("SelectAllCategory", sb);
            MyCommand.CommandType = CommandType.StoredProcedure;
            sb.Open();
            MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return MyReader;
        }
    }
}